package cdu.javaendwork.dao.impl;

import cdu.javaendwork.dao.BaseDao;
import cdu.javaendwork.dao.Universal;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.*;
import java.util.Map.Entry;


public class UniversalImpl extends BaseDao implements Universal{
    static HashMap<String, String> registry = new HashMap<>();
    private String pro_class_name = "";

    public UniversalImpl()
    {
        loadRegistry();
    }

    public void loadRegistry() {
        //加载配置文件
        InputStream stream = UniversalImpl.class.getClassLoader().getResourceAsStream("register.properties");
        Properties properties = new Properties();
        try {
            properties.load(stream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //构造注册表
        Iterator<Entry<Object, Object>> it = properties.entrySet().iterator();
        while (it.hasNext()) {
            Entry<Object, Object> entry = it.next();
            String key = (String) entry.getKey();
            String value = (String) entry.getValue();
            registry.put(key, value);
        }
        this.pro_class_name = registry.get("Pro_class_name");
    }

    //单查通过ID单查GetById
    //传进来一个具体的类 返回一个泛型 泛型方法
    public <T> T getById(String objName , int id)
    {
        T model = null;


        String sql="SELECT * FROM "  + registry.get(objName) + " WHERE id=?";

        try {

            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs=pstmt.executeQuery();
            try {
                model = (T) Class.forName(pro_class_name + objName).newInstance();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            ResultSetMetaData rsmd = rs.getMetaData();
            int cnt = rsmd.getColumnCount();
            while (rs.next()){
                for (int i = 1; i <= cnt; i++) {
                    //获取列值
                    Object colValue = rs.getObject(i);
                    //获取列名
                    String colName = rsmd.getColumnLabel(i);

                    //给对象指定的columnName属性 赋值为COLUMValue
                    Field field = null;
                    try {
                        field = Class.forName(pro_class_name+objName).getDeclaredField(colName);
                        // 将数据库中查询到的datetime类型转换成Java中对应的java.time.LocalDateTime类型数据
                        if (colValue.getClass().toString().equals("class java.time.LocalDateTime")) {
                            colValue = Timestamp.valueOf((LocalDateTime) colValue);
                        }
                    } catch (NoSuchFieldException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    field.setAccessible(true);
                    try {
                        field.set(model , colValue);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
            return model;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
    //全查All 泛型方法
    public <T> List<T> all(String objName)
    {

        ArrayList<T> list = new ArrayList<T>();
        String sql="SELECT * FROM "  + registry.get(objName);

        try {

            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            int cnt = rsmd.getColumnCount();
            while (rs.next()){
                T model = null;
                try {
                    model = (T) Class.forName(pro_class_name + objName).newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
                for (int i = 1; i <= cnt; i++) {
                    //获取列值
                    Object colValue = rs.getObject(i);
                    //获取列名
                    String colName = rsmd.getColumnLabel(i);

                    //给对象指定的columnName属性 赋值为COLUMValue
                    Field field = null;
                    try {
                        field = Class.forName(pro_class_name+objName).getDeclaredField(colName);
                        // 将数据库中查询到的datetime类型转换成Java中对应的java.time.LocalDateTime类型数据
                        if (colValue.getClass().toString().equals("class java.time.LocalDateTime")) {
                            colValue = Timestamp.valueOf((LocalDateTime) colValue);
                        }
                    } catch (NoSuchFieldException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    field.setAccessible(true);
                    try {
                        field.set(model , colValue);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                list.add(model);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
    //分页查Limit
    public <T> List<T> limit(String objName,int pageIndex , int pageSize)
    {
        ArrayList<T> list = new ArrayList<T>();
        String sql="SELECT * FROM " + registry.get(objName) + " limit ? , ?";

        try {

            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,(pageIndex-1)*pageSize);
            pstmt.setInt(2,pageSize);
            rs=pstmt.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            int cnt = rsmd.getColumnCount();
            while (rs.next()){
                T model = null;
                //泛型model获取
                try {
                    model = (T) Class.forName(pro_class_name + objName).newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
                for (int i = 1; i <= cnt; i++) {
                    //获取列值
                    Object colValue = rs.getObject(i);
                    //获取列名
                    String colName = rsmd.getColumnLabel(i);

                    //给对象指定的columnName属性 赋值为COLUMValue
                    Field field = null;
                    try {
                        field = Class.forName(pro_class_name+objName).getDeclaredField(colName);
                        // 将数据库中查询到的datetime类型转换成Java中对应的java.time.LocalDateTime类型数据
                        if (colValue.getClass().toString().equals("class java.time.LocalDateTime")) {
                            colValue = Timestamp.valueOf((LocalDateTime) colValue);
                        }
                    } catch (NoSuchFieldException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    field.setAccessible(true);
                    try {
                        field.set(model , colValue);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                list.add(model);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
    //查询总共的信息数量
    public int findSumCount(String objName)
    {
        int sumCount = 0;

        String sql="SELECT count(*) FROM " + registry.get(objName);

        try {
            pstmt=conn.prepareStatement(sql);

            rs=pstmt.executeQuery();

            if(rs.next())
            {
                sumCount = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sumCount;
    }
    //条件查询数量
    public int filterFindSumCount(String objName , String factor)
    {
        int sumCount = 0;

        String sql="SELECT count(*) FROM " + registry.get(objName) + " WHERE " + factor;

        try {
            pstmt=conn.prepareStatement(sql);

            rs=pstmt.executeQuery();

            if(rs.next())
            {
                sumCount = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return sumCount;
    }
    // 增
    public int insert(String objName,Object...args)
    {
        int rows = 0;
        int n = args.length;
        String table = registry.get(objName);
        String keys = registry.get(objName + "_List");
        String sql="INSERT INTO "+ table +"("+ keys +") VALUES(";
        //填充问号
        for (int i = 0; i < n; i++) {
            if(i != n-1) sql += "?,";
            else sql += "?)";
        }
//        System.out.println(sql);
        try {
            pstmt=conn.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < n; i++) {
                pstmt.setObject(i+1,args[i]);
            }

            rows=pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rows;
    }

    // 修
    public int updateById(String objName, int id,Object...args)
    {
        int rows = 0;
        int n = args.length;
        String table = registry.get(objName);
        String keys = registry.get(objName + "_List");
        keys += ",";
        String sql="UPDATE "+ table +" SET ";
        //username=?,password=?,age=?,sex=?,hobby=? WHERE id=?
        //填充问号和字段
        for (int i = 0 , j = 0; j<keys.length(); j++) {
            if(keys.charAt(j) == ',')
            {
                String t = keys.substring(i,j);
                if(j != keys.length()-1) t += "=?,";
                else t += "=? WHERE id=?";
                sql += t;
                i = j + 1;
            }
        }

        try {
            pstmt=conn.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < n; i++) {
                pstmt.setObject(i+1,args[i]);
            }
            pstmt.setInt(n+1,id);

            rows=pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rows;
    }

    // 通过id删 deleteById
    public int deleteById(String objName,int id)
    {
        int rows=0;

        String sql="DELETE FROM "+ registry.get(objName) +" WHERE id=?";

        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);

            rows=pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rows;
    }
    //模糊分页查询filterLimit
    public <T> List<T> filterLimit(String objName, String factor ,int pageIndex , int pageSize)
    {
        ArrayList<T> list = new ArrayList<T>();
        String sql="SELECT * FROM " + registry.get(objName) + " WHERE " +factor +" limit ? , ?";

        try {

            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,(pageIndex-1)*pageSize);
            pstmt.setInt(2,pageSize);
            rs=pstmt.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            int cnt = rsmd.getColumnCount();
            while (rs.next()){
                T model = null;
                //泛型model获取
                try {
                    model = (T) Class.forName(pro_class_name + objName).newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
                for (int i = 1; i <= cnt; i++) {
                    //获取列值
                    Object colValue = rs.getObject(i);
                    //获取列名
                    String colName = rsmd.getColumnLabel(i);

                    //给对象指定的columnName属性 赋值为COLUMValue
                    Field field = null;
                    try {
                        field = Class.forName(pro_class_name+objName).getDeclaredField(colName);
                        // 将数据库中查询到的datetime类型转换成Java中对应的java.time.LocalDateTime类型数据
                        if (colValue.getClass().toString().equals("class java.time.LocalDateTime")) {
                            colValue = Timestamp.valueOf((LocalDateTime) colValue);
                        }

                    } catch (NoSuchFieldException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    field.setAccessible(true);
                    try {
                        field.set(model , colValue);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                list.add(model);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    //全查All 泛型方法
    public <T> List<T> filter(String objName , String factor)
    {

        ArrayList<T> list = new ArrayList<T>();
        String sql="SELECT * FROM "  + registry.get(objName) + " WHERE " + factor;

        try {

            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            int cnt = rsmd.getColumnCount();
            while (rs.next()){
                T model = null;
                try {
                    model = (T) Class.forName(pro_class_name + objName).newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
                for (int i = 1; i <= cnt; i++) {
                    //获取列值
                    Object colValue = rs.getObject(i);
                    //获取列名
                    String colName = rsmd.getColumnLabel(i);

                    //给对象指定的columnName属性 赋值为COLUMValue
                    Field field = null;
                    try {
                        field = Class.forName(pro_class_name+objName).getDeclaredField(colName);
                        // 将数据库中查询到的datetime类型转换成Java中对应的java.time.LocalDateTime类型数据
                        if (colValue.getClass().toString().equals("class java.time.LocalDateTime")) {
                            colValue = Timestamp.valueOf((LocalDateTime) colValue);
                        }
                    } catch (NoSuchFieldException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    field.setAccessible(true);
                    try {
                        field.set(model , colValue);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                list.add(model);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
//    ==================使用参考===================
//    public static void main(String[] args) {
////          必须做的一步 ,new出通用对象
//        Universal u = new UniversalImpl();
////        ----------使用------------
////        System.out.println(registry);
////        System.out.println(registry.get("User"));
////        u.getById("User",12);
////        u.limit("User" , 1 , 2);
////        System.out.println("SumCount: "+u.findSumCount("User"));
////        u.insert("User" , "全能测试" , "123" , "12"  ,"男" ,"[打胶]");
////        u.updateById("User" , 20 , "全能测试" , "1234" , "12"  ,"男" ,"[打胶]");
////        u.deleteById("User" , 20);
////        u.all("User");
////        u.filterLimit("Product" , "name LIKE '%设计%'" , 1 , 5);
////        u.filter("Product" , "name LIKE '%模式%'");
////        u.filterFindSumCount("Product" , "name LIKE '%大话设计%'");
//    }

}
